﻿using System;
using System.Data;
using System.Data.SqlClient;

namespace EHSubmit.Util
{ 
    /// <summary>
    /// 封装分页操作
    /// </summary>
    public class PagedDataProvider
    {
        private string _tbName;                                    //表名    如表1 (不用加[])  
        private string _fieldsToReturn;                            //需要返回的列， 默认值为*   如列1，列2,.....
        private string _fieldToOrder;                              //排序的字段名  
        private int _pageSize;                                     //页尺寸， 默认值为10
        private int _pageIndex;                                    //页码， 默认值为1
        private bool _doCount;                                     //返回记录总数, 非 0 值则返回
        private bool _isDesc;                                      //设置排序类型
        private string _criteria;                                  //指定的数据筛选条件。默认为"1=1" (注意: 不要加 where)

        private int _totalCount;                                   //返回的记录总数

        /// <summary>
        /// 构造函数
        /// </summary>
        public PagedDataProvider()
	    {
            this.Init();
	    }

        /// <summary>
        /// 初始化
        /// </summary>
        /// <returns>初始化成功返回真</returns>
        public bool Init()
        {
            this._tbName = string.Empty;
            this._fieldsToReturn = "*";
            this._fieldToOrder = string.Empty;
            this._pageSize = 10;
            this._pageIndex = 1;
            this._doCount = false;
            this._isDesc = false;
            this._criteria = "1 = 1";

            this._totalCount = 0;
            return true;
        }

        #region 常量声明
        /// <summary>
        /// 表名   如表1 (不用加[])  
        /// </summary>
        public string TbName
        {
            set { _tbName = value; }
        }

        /// <summary>
        /// 需要返回的列    如列1，列2,.....
        /// </summary>
        public string FieldsToReturn
        {
            set { _fieldsToReturn = value; }
        }

        /// <summary>
        /// 用于排序的字段名称，一般为主键或者标识列。必须指定。 
        /// </summary>
        public string FieldToOrder
        {
            set { _fieldToOrder = value; }
        }

        /// <summary>
        /// 页尺寸， 默认值为10
        /// </summary>
        public int PageSize 
        {
            set { _pageSize = value; }
        }

        /// <summary>
        /// 页码， 默认值为1
        /// </summary>
        public int PageIndex
        {
            set { _pageIndex = value; }
        }

        /// <summary>
        /// 返回记录总数, true值则返回
        /// </summary>
        public bool DoCount 
        {
            set { _doCount = value; }
        }

        /// <summary>
        /// 设置排序类型
        /// </summary>
        public bool IsDesc 
        {
            set { _isDesc = value; }
        }

        /// <summary>
        /// 指定的数据筛选条件。默认为"1=1" (注意: 不要加 where)
        /// </summary>
        public string Criteria 
        {
            set { _criteria = value; }
        }

        /// <summary>
        /// 返回的记录总数
        /// </summary>
        public int TotalCount
        {
            get { return _totalCount; }
            set { _totalCount = value; }
        }
        #endregion

        /// <summary>
        /// 获取单页结果
        /// </summary>
        /// <returns>返回的SqlDataReader</returns>
        public SqlDataReader GetResults()
        {
            try
            {
                if (_tbName == null || _tbName == string.Empty)
                {
                    throw new Exception("表名必须指定");
                }
                else
                {
                    SqlParameter[] parms = new SqlParameter[8];
                    parms[0] = new SqlParameter("@tbName", SqlDbType.NVarChar, 255);                   //表名
                    parms[1] = new SqlParameter("@fieldsToReturn", SqlDbType.NVarChar, 255);           //需要返回的列    如列1，列2,.....
                    parms[2] = new SqlParameter("@fieldToOrder", SqlDbType.NVarChar, 64);              //排序的字段名    如表1 (不用加[])  
                    parms[3] = new SqlParameter("@pageSize", SqlDbType.Int);                           //页尺寸
                    parms[4] = new SqlParameter("@pageIndex", SqlDbType.Int);                          //页码
                    parms[5] = new SqlParameter("@doCount", SqlDbType.Bit);                            //是否返回记录总数, 非 0 值则返回
                    parms[6] = new SqlParameter("@IsDesc", SqlDbType.Bit);                             //设置排序类型
                    parms[7] = new SqlParameter("@criteria", SqlDbType.NVarChar, 255);                 //指定的数据筛选条件。默认为"1=1" (注意: 不要加 where)

                    parms[0].Value = _tbName;
                    parms[1].Value = _fieldsToReturn;
                    parms[2].Value = _fieldToOrder;
                    parms[3].Value = _pageSize;
                    parms[4].Value = _pageIndex;
                    parms[5].Value = _doCount;
                    parms[6].Value = _isDesc;
                    parms[7].Value = _criteria;

                    for (int i = 0; i < parms.Length; i++)
                    {
                        parms[i].Direction = ParameterDirection.Input;
                    }

                    SqlDataReader results = (SqlDataReader)SqlHelper.ExecuteReader(SqlHelper.ConnectionStringLocalTransaction, CommandType.StoredProcedure, "Pagenation", parms);
                    return results;
                }
            }
            catch (Exception ex)
            {
                //有待处理
                LogHelper.Log(ex.Message, ex);
                return null;
            }
      
        }

        ///// <summary>
        ///// 返回总记录数（原来的，用了两个存储过程）
        ///// </summary>
        ///// <returns>总记录数</returns>
        //public int GetRecordCount()
        //{
        //    try
        //    {
        //        if (_tbName == null || _tbName == string.Empty)
        //        {
        //            throw new Exception("表名必须指定");
        //        }
        //        else
        //        {
        //            SqlParameter[] parms = new SqlParameter[3];
        //            parms[0] = new SqlParameter("@tbName", SqlDbType.NVarChar, 255);                   //表名
        //            parms[6] = new SqlParameter("@criteria", SqlDbType.NVarChar, 255);                 //查询条件 (注意: 不要加 where)
        //            parms[2] = new SqlParameter("@totalCount", SqlDbType.Int);                         //返回的记录总数

        //            parms[0].Value = _tbName;
        //            parms[1].Value = _fieldToOrder;
        //            parms[2].Direction = ParameterDirection.Output;
        //            int nResult = (int)SqlHelper.ExecuteNonQuery(SqlHelper.ConnectionStringLocalTransaction, CommandType.StoredProcedure, "GetRecordCount", parms);
        //            _totalCount = (int)parms[2].Value;
        //            return _totalCount;
        //        }
        //    }
        //    catch (Exception ex)
        //    {
        //        //有待处理
        //        return -1;
        //    }

        /// <summary>
        /// 返回总记录数
        /// </summary>
        /// <returns>如果查找失败返回-1，否则返回总记录数</returns>
        public int GetRecordCount()
        {
            if (_tbName == null || _tbName == string.Empty)
            {
                return -1;  
            }
            else
            {
                _doCount = true;
                SqlParameter[] parms = new SqlParameter[3];
                parms[0] = new SqlParameter("@tbName", SqlDbType.NVarChar, 255);                   //表名
                parms[1] = new SqlParameter("@doCount", SqlDbType.Bit);                            //是否返回记录总数, 非 0 值则返回
                parms[2] = new SqlParameter("@criteria", SqlDbType.NVarChar, 255);                 //指定的数据筛选条件。默认为"1=1" (注意: 不要加 where)

                parms[0].Value = _tbName;
                parms[1].Value = _doCount;
                parms[2].Value = _criteria;
                _totalCount = 0;
                using(SqlDataReader rdrCount = (SqlDataReader)SqlHelper.ExecuteReader(SqlHelper.ConnectionStringLocalTransaction, CommandType.StoredProcedure, "Pagenation", parms))
                {
                    if (rdrCount.Read())    //返回总记录数
                    {
                        _totalCount = (int)rdrCount["TotalCount"];
                    }
                }
                return _totalCount;
            }
        }
    }
}